{
 "cells": [
  {
   "cell_type": "code",
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2024-05-12T18:56:35.142197Z",
     "start_time": "2024-05-12T18:56:34.253931Z"
    }
   },
   "source": [
    "from tqdm import tqdm\n",
    "\n",
    "import pandas as pd\n",
    "import tushare as ts\n",
    "\n",
    "from data.download.macro import get_marco_data\n",
    "from data.download.financial import get_financial_data\n",
    "from data.download.pledge import get_pledge_detail_data, get_pledge_summary_data"
   ],
   "outputs": [],
   "execution_count": 1
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-05-12T18:56:35.157709Z",
     "start_time": "2024-05-12T18:56:35.143198Z"
    }
   },
   "cell_type": "code",
   "source": [
    "ts.set_token('753987624b84d7429640bd00016924e00fea8a9305645c3f29e3bb1a')\n",
    "pro = ts.pro_api()"
   ],
   "id": "55227e06c150c4ae",
   "outputs": [],
   "execution_count": 2
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-05-12T18:56:36.081380Z",
     "start_time": "2024-05-12T18:56:35.158710Z"
    }
   },
   "cell_type": "code",
   "source": [
    "try:\n",
    "    all_stock_df = pro.stock_basic(exchange='', list_status='L', fields='ts_code,symbol,name,list_date,list_status')\n",
    "    candidate_stock_df = all_stock_df[\n",
    "        (all_stock_df[\"list_date\"] <= \"20200101\") &\n",
    "        (all_stock_df[\"list_status\"] == \"L\")\n",
    "        ]\n",
    "    candidate_stock_df.to_excel(\"candidate_stock.xlsx\", index=False)\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "    candidate_stock_df = pd.read_excel(\"candidate_stock.xlsx\")\n",
    "\n",
    "candidate_stock_df"
   ],
   "id": "e18dd7f8e20bc122",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        ts_code  symbol   name list_status list_date\n",
       "0     000001.SZ  000001   平安银行           L  19910403\n",
       "1     000002.SZ  000002    万科A           L  19910129\n",
       "2     000004.SZ  000004   国华网安           L  19910114\n",
       "3     000006.SZ  000006   深振业A           L  19920427\n",
       "4     000007.SZ  000007  *ST全新           L  19920413\n",
       "...         ...     ...    ...         ...       ...\n",
       "4877  688368.SH  688368   晶丰明源           L  20191014\n",
       "4878  688369.SH  688369   致远互联           L  20191031\n",
       "4895  688388.SH  688388   嘉元科技           L  20190722\n",
       "4896  688389.SH  688389   普门科技           L  20191105\n",
       "4904  688399.SH  688399   硕世生物           L  20191205\n",
       "\n",
       "[3629 rows x 5 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>list_status</th>\n",
       "      <th>list_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>000001</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>L</td>\n",
       "      <td>19910403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000002.SZ</td>\n",
       "      <td>000002</td>\n",
       "      <td>万科A</td>\n",
       "      <td>L</td>\n",
       "      <td>19910129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000004.SZ</td>\n",
       "      <td>000004</td>\n",
       "      <td>国华网安</td>\n",
       "      <td>L</td>\n",
       "      <td>19910114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000006.SZ</td>\n",
       "      <td>000006</td>\n",
       "      <td>深振业A</td>\n",
       "      <td>L</td>\n",
       "      <td>19920427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000007.SZ</td>\n",
       "      <td>000007</td>\n",
       "      <td>*ST全新</td>\n",
       "      <td>L</td>\n",
       "      <td>19920413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4877</th>\n",
       "      <td>688368.SH</td>\n",
       "      <td>688368</td>\n",
       "      <td>晶丰明源</td>\n",
       "      <td>L</td>\n",
       "      <td>20191014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4878</th>\n",
       "      <td>688369.SH</td>\n",
       "      <td>688369</td>\n",
       "      <td>致远互联</td>\n",
       "      <td>L</td>\n",
       "      <td>20191031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4895</th>\n",
       "      <td>688388.SH</td>\n",
       "      <td>688388</td>\n",
       "      <td>嘉元科技</td>\n",
       "      <td>L</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4896</th>\n",
       "      <td>688389.SH</td>\n",
       "      <td>688389</td>\n",
       "      <td>普门科技</td>\n",
       "      <td>L</td>\n",
       "      <td>20191105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4904</th>\n",
       "      <td>688399.SH</td>\n",
       "      <td>688399</td>\n",
       "      <td>硕世生物</td>\n",
       "      <td>L</td>\n",
       "      <td>20191205</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3629 rows × 5 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 3
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-05-12T18:57:12.891823Z",
     "start_time": "2024-05-12T18:57:12.876824Z"
    }
   },
   "cell_type": "code",
   "source": [
    "def run_single_pledge(pledge, marco_data, pledge_summary, financial_data):\n",
    "    date = pledge[\"质押日期\"]\n",
    "\n",
    "    marco_data = marco_data[marco_data[\"date\"] < date].iloc[0]\n",
    "    del marco_data[\"date\"]\n",
    "\n",
    "    pledge_summary_info = pledge_summary[pledge_summary[\"质押日期\"] < date].iloc[0]\n",
    "    del pledge_summary_info[\"质押日期\"]\n",
    "\n",
    "    financial_data = financial_data[financial_data[\"时间\"] < date].iloc[0]\n",
    "    del financial_data[\"时间\"]\n",
    "\n",
    "    return (\n",
    "        pledge_summary_info,\n",
    "        financial_data,\n",
    "        marco_data\n",
    "    )\n",
    "\n",
    "\n",
    "def run_single_stock(stock_code, marco_data):\n",
    "    result = []\n",
    "\n",
    "    all_pledge = get_pledge_detail_data(stock_code)\n",
    "    all_pledge = all_pledge[all_pledge[\"质押日期\"] > \"2020-03-01\"]\n",
    "    all_pledge = all_pledge.to_dict(orient='records')\n",
    "    pledge_summary = get_pledge_summary_data(stock_code)\n",
    "    financial_data = get_financial_data(stock_code)\n",
    "\n",
    "    for pledge in all_pledge:\n",
    "        for i in run_single_pledge(pledge, marco_data, pledge_summary, financial_data):\n",
    "            pledge.update(i)\n",
    "\n",
    "        result.append(pledge)\n",
    "\n",
    "    return all_pledge\n",
    "\n"
   ],
   "id": "f2117c9d40763916",
   "outputs": [],
   "execution_count": 4
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-05-12T19:59:02.199423Z",
     "start_time": "2024-05-12T18:57:58.109241Z"
    }
   },
   "cell_type": "code",
   "source": [
    "result = []\n",
    "marco_data = get_marco_data()\n",
    "\n",
    "for stock_code in tqdm(candidate_stock_df[\"ts_code\"].values):\n",
    "    try:\n",
    "        result += run_single_stock(stock_code.split(\".\")[0], marco_data)\n",
    "    except Exception as e:\n",
    "        pass\n",
    "\n",
    "df = pd.DataFrame(result)\n",
    "\n",
    "df"
   ],
   "id": "72651af9346edb82",
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 3629/3629 [1:01:03<00:00,  1.01s/it]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "                      质押日期 是否为控股股东      质押市值（万元）     质押股数（万）  占所持股份比例（%）  \\\n",
       "0      2022-09-28 00:00:00       0  5.178791e+05   5973.7200     13.6200   \n",
       "1      2022-09-08 00:00:00       0  9.216243e+05  18588.0000     42.3700   \n",
       "2      2022-05-24 00:00:00       0  1.005149e+06  10899.1900     89.1700   \n",
       "3      2021-09-02 00:00:00       0  6.366015e+05  12631.5789     28.7929   \n",
       "4      2021-09-02 00:00:00       0  6.366015e+05   7368.4211     16.7959   \n",
       "...                    ...     ...           ...         ...         ...   \n",
       "27708  2020-06-24 00:00:00       0  9.986491e+04    695.5000      4.4578   \n",
       "27709  2020-06-24 00:00:00       0  9.986491e+04    740.0000      4.7400   \n",
       "27710  2020-06-24 00:00:00       0  9.986491e+04   1120.3636      7.1800   \n",
       "27711  2020-06-04 00:00:00       0  1.112895e+05   4375.0000     28.0414   \n",
       "27712  2020-05-27 00:00:00       0  1.115965e+05    625.0000      4.0100   \n",
       "\n",
       "       占所总股本比例（%）   质押状态  质押比例（%）  质押笔数  限售股质押股数（万）  ...  毛利率(%)  总资产报酬率(%)  \\\n",
       "0          0.5100  达到平仓线     3.04     4         0.0  ...   20.46      0.640   \n",
       "1          1.6000  达到平仓线     5.64    16         0.0  ...   20.46      0.640   \n",
       "2          0.9400  达到平仓线     5.47    17         0.0  ...   18.87      0.074   \n",
       "3          1.0861  达到平仓线     3.13     9         0.0  ...   22.94      0.578   \n",
       "4          0.6335  达到平仓线     3.13     9         0.0  ...   22.94      0.578   \n",
       "...           ...    ...      ...   ...         ...  ...     ...        ...   \n",
       "27708      1.6196  未达预警线    34.41    15         0.0  ...   60.46      0.968   \n",
       "27709      1.7200  未达预警线    34.41    15         0.0  ...   60.46      0.968   \n",
       "27710      2.6100  未达预警线    34.41    15         0.0  ...   60.46      0.968   \n",
       "27711      9.9522  未达预警线    40.21    19         0.0  ...   60.46      0.968   \n",
       "27712      1.4200  未达预警线    41.03    19         0.0  ...   60.46      0.968   \n",
       "\n",
       "       营业收入增长率(%)  总资产增长率(%)  营业利润增长率(%)  净利润增长率(%)  净资产增长率(%)  GDPg  CPIg  \\\n",
       "0          23.820     -3.066      10.234     10.643     -0.728   3.0  2.67   \n",
       "1          23.820     -3.066      10.234     10.643     -0.728   3.0  2.67   \n",
       "2           0.647      0.450      -1.863     10.579      0.389   4.8  1.10   \n",
       "3          14.186      4.594     -17.228    -11.678     -1.178   9.8  0.83   \n",
       "4          14.186      4.594     -17.228    -11.678     -1.178   9.8  0.83   \n",
       "...           ...        ...         ...        ...        ...   ...   ...   \n",
       "27708      10.766      3.955     -34.713    -17.648      1.748  -1.6  2.73   \n",
       "27709      10.766      3.955     -34.713    -17.648      1.748  -1.6  2.73   \n",
       "27710      10.766      3.955     -34.713    -17.648      1.748  -1.6  2.73   \n",
       "27711      10.766      3.955     -34.713    -17.648      1.748  -1.6  2.73   \n",
       "27712      10.766      3.955     -34.713    -17.648      1.748  -6.8  4.97   \n",
       "\n",
       "       PPIg  \n",
       "0      2.47  \n",
       "1      2.47  \n",
       "2      8.73  \n",
       "3      9.73  \n",
       "4      9.73  \n",
       "...     ...  \n",
       "27708 -3.27  \n",
       "27709 -3.27  \n",
       "27710 -3.27  \n",
       "27711 -3.27  \n",
       "27712 -0.60  \n",
       "\n",
       "[27713 rows x 35 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>质押日期</th>\n",
       "      <th>是否为控股股东</th>\n",
       "      <th>质押市值（万元）</th>\n",
       "      <th>质押股数（万）</th>\n",
       "      <th>占所持股份比例（%）</th>\n",
       "      <th>占所总股本比例（%）</th>\n",
       "      <th>质押状态</th>\n",
       "      <th>质押比例（%）</th>\n",
       "      <th>质押笔数</th>\n",
       "      <th>限售股质押股数（万）</th>\n",
       "      <th>...</th>\n",
       "      <th>毛利率(%)</th>\n",
       "      <th>总资产报酬率(%)</th>\n",
       "      <th>营业收入增长率(%)</th>\n",
       "      <th>总资产增长率(%)</th>\n",
       "      <th>营业利润增长率(%)</th>\n",
       "      <th>净利润增长率(%)</th>\n",
       "      <th>净资产增长率(%)</th>\n",
       "      <th>GDPg</th>\n",
       "      <th>CPIg</th>\n",
       "      <th>PPIg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-09-28 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>5.178791e+05</td>\n",
       "      <td>5973.7200</td>\n",
       "      <td>13.6200</td>\n",
       "      <td>0.5100</td>\n",
       "      <td>达到平仓线</td>\n",
       "      <td>3.04</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>20.46</td>\n",
       "      <td>0.640</td>\n",
       "      <td>23.820</td>\n",
       "      <td>-3.066</td>\n",
       "      <td>10.234</td>\n",
       "      <td>10.643</td>\n",
       "      <td>-0.728</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.67</td>\n",
       "      <td>2.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-09-08 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>9.216243e+05</td>\n",
       "      <td>18588.0000</td>\n",
       "      <td>42.3700</td>\n",
       "      <td>1.6000</td>\n",
       "      <td>达到平仓线</td>\n",
       "      <td>5.64</td>\n",
       "      <td>16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>20.46</td>\n",
       "      <td>0.640</td>\n",
       "      <td>23.820</td>\n",
       "      <td>-3.066</td>\n",
       "      <td>10.234</td>\n",
       "      <td>10.643</td>\n",
       "      <td>-0.728</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.67</td>\n",
       "      <td>2.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>1.005149e+06</td>\n",
       "      <td>10899.1900</td>\n",
       "      <td>89.1700</td>\n",
       "      <td>0.9400</td>\n",
       "      <td>达到平仓线</td>\n",
       "      <td>5.47</td>\n",
       "      <td>17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>18.87</td>\n",
       "      <td>0.074</td>\n",
       "      <td>0.647</td>\n",
       "      <td>0.450</td>\n",
       "      <td>-1.863</td>\n",
       "      <td>10.579</td>\n",
       "      <td>0.389</td>\n",
       "      <td>4.8</td>\n",
       "      <td>1.10</td>\n",
       "      <td>8.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-09-02 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>6.366015e+05</td>\n",
       "      <td>12631.5789</td>\n",
       "      <td>28.7929</td>\n",
       "      <td>1.0861</td>\n",
       "      <td>达到平仓线</td>\n",
       "      <td>3.13</td>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>22.94</td>\n",
       "      <td>0.578</td>\n",
       "      <td>14.186</td>\n",
       "      <td>4.594</td>\n",
       "      <td>-17.228</td>\n",
       "      <td>-11.678</td>\n",
       "      <td>-1.178</td>\n",
       "      <td>9.8</td>\n",
       "      <td>0.83</td>\n",
       "      <td>9.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-09-02 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>6.366015e+05</td>\n",
       "      <td>7368.4211</td>\n",
       "      <td>16.7959</td>\n",
       "      <td>0.6335</td>\n",
       "      <td>达到平仓线</td>\n",
       "      <td>3.13</td>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>22.94</td>\n",
       "      <td>0.578</td>\n",
       "      <td>14.186</td>\n",
       "      <td>4.594</td>\n",
       "      <td>-17.228</td>\n",
       "      <td>-11.678</td>\n",
       "      <td>-1.178</td>\n",
       "      <td>9.8</td>\n",
       "      <td>0.83</td>\n",
       "      <td>9.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27708</th>\n",
       "      <td>2020-06-24 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>9.986491e+04</td>\n",
       "      <td>695.5000</td>\n",
       "      <td>4.4578</td>\n",
       "      <td>1.6196</td>\n",
       "      <td>未达预警线</td>\n",
       "      <td>34.41</td>\n",
       "      <td>15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>60.46</td>\n",
       "      <td>0.968</td>\n",
       "      <td>10.766</td>\n",
       "      <td>3.955</td>\n",
       "      <td>-34.713</td>\n",
       "      <td>-17.648</td>\n",
       "      <td>1.748</td>\n",
       "      <td>-1.6</td>\n",
       "      <td>2.73</td>\n",
       "      <td>-3.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27709</th>\n",
       "      <td>2020-06-24 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>9.986491e+04</td>\n",
       "      <td>740.0000</td>\n",
       "      <td>4.7400</td>\n",
       "      <td>1.7200</td>\n",
       "      <td>未达预警线</td>\n",
       "      <td>34.41</td>\n",
       "      <td>15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>60.46</td>\n",
       "      <td>0.968</td>\n",
       "      <td>10.766</td>\n",
       "      <td>3.955</td>\n",
       "      <td>-34.713</td>\n",
       "      <td>-17.648</td>\n",
       "      <td>1.748</td>\n",
       "      <td>-1.6</td>\n",
       "      <td>2.73</td>\n",
       "      <td>-3.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27710</th>\n",
       "      <td>2020-06-24 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>9.986491e+04</td>\n",
       "      <td>1120.3636</td>\n",
       "      <td>7.1800</td>\n",
       "      <td>2.6100</td>\n",
       "      <td>未达预警线</td>\n",
       "      <td>34.41</td>\n",
       "      <td>15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>60.46</td>\n",
       "      <td>0.968</td>\n",
       "      <td>10.766</td>\n",
       "      <td>3.955</td>\n",
       "      <td>-34.713</td>\n",
       "      <td>-17.648</td>\n",
       "      <td>1.748</td>\n",
       "      <td>-1.6</td>\n",
       "      <td>2.73</td>\n",
       "      <td>-3.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27711</th>\n",
       "      <td>2020-06-04 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>1.112895e+05</td>\n",
       "      <td>4375.0000</td>\n",
       "      <td>28.0414</td>\n",
       "      <td>9.9522</td>\n",
       "      <td>未达预警线</td>\n",
       "      <td>40.21</td>\n",
       "      <td>19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>60.46</td>\n",
       "      <td>0.968</td>\n",
       "      <td>10.766</td>\n",
       "      <td>3.955</td>\n",
       "      <td>-34.713</td>\n",
       "      <td>-17.648</td>\n",
       "      <td>1.748</td>\n",
       "      <td>-1.6</td>\n",
       "      <td>2.73</td>\n",
       "      <td>-3.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27712</th>\n",
       "      <td>2020-05-27 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>1.115965e+05</td>\n",
       "      <td>625.0000</td>\n",
       "      <td>4.0100</td>\n",
       "      <td>1.4200</td>\n",
       "      <td>未达预警线</td>\n",
       "      <td>41.03</td>\n",
       "      <td>19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>60.46</td>\n",
       "      <td>0.968</td>\n",
       "      <td>10.766</td>\n",
       "      <td>3.955</td>\n",
       "      <td>-34.713</td>\n",
       "      <td>-17.648</td>\n",
       "      <td>1.748</td>\n",
       "      <td>-6.8</td>\n",
       "      <td>4.97</td>\n",
       "      <td>-0.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27713 rows × 35 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 6
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-05-13T04:21:15.552528Z",
     "start_time": "2024-05-13T04:21:15.514529Z"
    }
   },
   "cell_type": "code",
   "source": [
    "all_data = df.dropna()\n",
    "all_data"
   ],
   "id": "e05e566ae622a018",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                      质押日期 是否为控股股东      质押市值（万元）     质押股数（万）  占所持股份比例（%）  \\\n",
       "0      2022-09-28 00:00:00       0  5.178791e+05   5973.7200     13.6200   \n",
       "1      2022-09-08 00:00:00       0  9.216243e+05  18588.0000     42.3700   \n",
       "2      2022-05-24 00:00:00       0  1.005149e+06  10899.1900     89.1700   \n",
       "3      2021-09-02 00:00:00       0  6.366015e+05  12631.5789     28.7929   \n",
       "4      2021-09-02 00:00:00       0  6.366015e+05   7368.4211     16.7959   \n",
       "...                    ...     ...           ...         ...         ...   \n",
       "27708  2020-06-24 00:00:00       0  9.986491e+04    695.5000      4.4578   \n",
       "27709  2020-06-24 00:00:00       0  9.986491e+04    740.0000      4.7400   \n",
       "27710  2020-06-24 00:00:00       0  9.986491e+04   1120.3636      7.1800   \n",
       "27711  2020-06-04 00:00:00       0  1.112895e+05   4375.0000     28.0414   \n",
       "27712  2020-05-27 00:00:00       0  1.115965e+05    625.0000      4.0100   \n",
       "\n",
       "       占所总股本比例（%）   质押状态  质押比例（%）  质押笔数  限售股质押股数（万）  ...  毛利率(%)  总资产报酬率(%)  \\\n",
       "0          0.5100  达到平仓线     3.04     4         0.0  ...   20.46      0.640   \n",
       "1          1.6000  达到平仓线     5.64    16         0.0  ...   20.46      0.640   \n",
       "2          0.9400  达到平仓线     5.47    17         0.0  ...   18.87      0.074   \n",
       "3          1.0861  达到平仓线     3.13     9         0.0  ...   22.94      0.578   \n",
       "4          0.6335  达到平仓线     3.13     9         0.0  ...   22.94      0.578   \n",
       "...           ...    ...      ...   ...         ...  ...     ...        ...   \n",
       "27708      1.6196  未达预警线    34.41    15         0.0  ...   60.46      0.968   \n",
       "27709      1.7200  未达预警线    34.41    15         0.0  ...   60.46      0.968   \n",
       "27710      2.6100  未达预警线    34.41    15         0.0  ...   60.46      0.968   \n",
       "27711      9.9522  未达预警线    40.21    19         0.0  ...   60.46      0.968   \n",
       "27712      1.4200  未达预警线    41.03    19         0.0  ...   60.46      0.968   \n",
       "\n",
       "       营业收入增长率(%)  总资产增长率(%)  营业利润增长率(%)  净利润增长率(%)  净资产增长率(%)  GDPg  CPIg  \\\n",
       "0          23.820     -3.066      10.234     10.643     -0.728   3.0  2.67   \n",
       "1          23.820     -3.066      10.234     10.643     -0.728   3.0  2.67   \n",
       "2           0.647      0.450      -1.863     10.579      0.389   4.8  1.10   \n",
       "3          14.186      4.594     -17.228    -11.678     -1.178   9.8  0.83   \n",
       "4          14.186      4.594     -17.228    -11.678     -1.178   9.8  0.83   \n",
       "...           ...        ...         ...        ...        ...   ...   ...   \n",
       "27708      10.766      3.955     -34.713    -17.648      1.748  -1.6  2.73   \n",
       "27709      10.766      3.955     -34.713    -17.648      1.748  -1.6  2.73   \n",
       "27710      10.766      3.955     -34.713    -17.648      1.748  -1.6  2.73   \n",
       "27711      10.766      3.955     -34.713    -17.648      1.748  -1.6  2.73   \n",
       "27712      10.766      3.955     -34.713    -17.648      1.748  -6.8  4.97   \n",
       "\n",
       "       PPIg  \n",
       "0      2.47  \n",
       "1      2.47  \n",
       "2      8.73  \n",
       "3      9.73  \n",
       "4      9.73  \n",
       "...     ...  \n",
       "27708 -3.27  \n",
       "27709 -3.27  \n",
       "27710 -3.27  \n",
       "27711 -3.27  \n",
       "27712 -0.60  \n",
       "\n",
       "[25954 rows x 35 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>质押日期</th>\n",
       "      <th>是否为控股股东</th>\n",
       "      <th>质押市值（万元）</th>\n",
       "      <th>质押股数（万）</th>\n",
       "      <th>占所持股份比例（%）</th>\n",
       "      <th>占所总股本比例（%）</th>\n",
       "      <th>质押状态</th>\n",
       "      <th>质押比例（%）</th>\n",
       "      <th>质押笔数</th>\n",
       "      <th>限售股质押股数（万）</th>\n",
       "      <th>...</th>\n",
       "      <th>毛利率(%)</th>\n",
       "      <th>总资产报酬率(%)</th>\n",
       "      <th>营业收入增长率(%)</th>\n",
       "      <th>总资产增长率(%)</th>\n",
       "      <th>营业利润增长率(%)</th>\n",
       "      <th>净利润增长率(%)</th>\n",
       "      <th>净资产增长率(%)</th>\n",
       "      <th>GDPg</th>\n",
       "      <th>CPIg</th>\n",
       "      <th>PPIg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-09-28 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>5.178791e+05</td>\n",
       "      <td>5973.7200</td>\n",
       "      <td>13.6200</td>\n",
       "      <td>0.5100</td>\n",
       "      <td>达到平仓线</td>\n",
       "      <td>3.04</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>20.46</td>\n",
       "      <td>0.640</td>\n",
       "      <td>23.820</td>\n",
       "      <td>-3.066</td>\n",
       "      <td>10.234</td>\n",
       "      <td>10.643</td>\n",
       "      <td>-0.728</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.67</td>\n",
       "      <td>2.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-09-08 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>9.216243e+05</td>\n",
       "      <td>18588.0000</td>\n",
       "      <td>42.3700</td>\n",
       "      <td>1.6000</td>\n",
       "      <td>达到平仓线</td>\n",
       "      <td>5.64</td>\n",
       "      <td>16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>20.46</td>\n",
       "      <td>0.640</td>\n",
       "      <td>23.820</td>\n",
       "      <td>-3.066</td>\n",
       "      <td>10.234</td>\n",
       "      <td>10.643</td>\n",
       "      <td>-0.728</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.67</td>\n",
       "      <td>2.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>1.005149e+06</td>\n",
       "      <td>10899.1900</td>\n",
       "      <td>89.1700</td>\n",
       "      <td>0.9400</td>\n",
       "      <td>达到平仓线</td>\n",
       "      <td>5.47</td>\n",
       "      <td>17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>18.87</td>\n",
       "      <td>0.074</td>\n",
       "      <td>0.647</td>\n",
       "      <td>0.450</td>\n",
       "      <td>-1.863</td>\n",
       "      <td>10.579</td>\n",
       "      <td>0.389</td>\n",
       "      <td>4.8</td>\n",
       "      <td>1.10</td>\n",
       "      <td>8.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-09-02 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>6.366015e+05</td>\n",
       "      <td>12631.5789</td>\n",
       "      <td>28.7929</td>\n",
       "      <td>1.0861</td>\n",
       "      <td>达到平仓线</td>\n",
       "      <td>3.13</td>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>22.94</td>\n",
       "      <td>0.578</td>\n",
       "      <td>14.186</td>\n",
       "      <td>4.594</td>\n",
       "      <td>-17.228</td>\n",
       "      <td>-11.678</td>\n",
       "      <td>-1.178</td>\n",
       "      <td>9.8</td>\n",
       "      <td>0.83</td>\n",
       "      <td>9.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-09-02 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>6.366015e+05</td>\n",
       "      <td>7368.4211</td>\n",
       "      <td>16.7959</td>\n",
       "      <td>0.6335</td>\n",
       "      <td>达到平仓线</td>\n",
       "      <td>3.13</td>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>22.94</td>\n",
       "      <td>0.578</td>\n",
       "      <td>14.186</td>\n",
       "      <td>4.594</td>\n",
       "      <td>-17.228</td>\n",
       "      <td>-11.678</td>\n",
       "      <td>-1.178</td>\n",
       "      <td>9.8</td>\n",
       "      <td>0.83</td>\n",
       "      <td>9.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27708</th>\n",
       "      <td>2020-06-24 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>9.986491e+04</td>\n",
       "      <td>695.5000</td>\n",
       "      <td>4.4578</td>\n",
       "      <td>1.6196</td>\n",
       "      <td>未达预警线</td>\n",
       "      <td>34.41</td>\n",
       "      <td>15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>60.46</td>\n",
       "      <td>0.968</td>\n",
       "      <td>10.766</td>\n",
       "      <td>3.955</td>\n",
       "      <td>-34.713</td>\n",
       "      <td>-17.648</td>\n",
       "      <td>1.748</td>\n",
       "      <td>-1.6</td>\n",
       "      <td>2.73</td>\n",
       "      <td>-3.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27709</th>\n",
       "      <td>2020-06-24 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>9.986491e+04</td>\n",
       "      <td>740.0000</td>\n",
       "      <td>4.7400</td>\n",
       "      <td>1.7200</td>\n",
       "      <td>未达预警线</td>\n",
       "      <td>34.41</td>\n",
       "      <td>15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>60.46</td>\n",
       "      <td>0.968</td>\n",
       "      <td>10.766</td>\n",
       "      <td>3.955</td>\n",
       "      <td>-34.713</td>\n",
       "      <td>-17.648</td>\n",
       "      <td>1.748</td>\n",
       "      <td>-1.6</td>\n",
       "      <td>2.73</td>\n",
       "      <td>-3.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27710</th>\n",
       "      <td>2020-06-24 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>9.986491e+04</td>\n",
       "      <td>1120.3636</td>\n",
       "      <td>7.1800</td>\n",
       "      <td>2.6100</td>\n",
       "      <td>未达预警线</td>\n",
       "      <td>34.41</td>\n",
       "      <td>15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>60.46</td>\n",
       "      <td>0.968</td>\n",
       "      <td>10.766</td>\n",
       "      <td>3.955</td>\n",
       "      <td>-34.713</td>\n",
       "      <td>-17.648</td>\n",
       "      <td>1.748</td>\n",
       "      <td>-1.6</td>\n",
       "      <td>2.73</td>\n",
       "      <td>-3.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27711</th>\n",
       "      <td>2020-06-04 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>1.112895e+05</td>\n",
       "      <td>4375.0000</td>\n",
       "      <td>28.0414</td>\n",
       "      <td>9.9522</td>\n",
       "      <td>未达预警线</td>\n",
       "      <td>40.21</td>\n",
       "      <td>19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>60.46</td>\n",
       "      <td>0.968</td>\n",
       "      <td>10.766</td>\n",
       "      <td>3.955</td>\n",
       "      <td>-34.713</td>\n",
       "      <td>-17.648</td>\n",
       "      <td>1.748</td>\n",
       "      <td>-1.6</td>\n",
       "      <td>2.73</td>\n",
       "      <td>-3.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27712</th>\n",
       "      <td>2020-05-27 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>1.115965e+05</td>\n",
       "      <td>625.0000</td>\n",
       "      <td>4.0100</td>\n",
       "      <td>1.4200</td>\n",
       "      <td>未达预警线</td>\n",
       "      <td>41.03</td>\n",
       "      <td>19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>60.46</td>\n",
       "      <td>0.968</td>\n",
       "      <td>10.766</td>\n",
       "      <td>3.955</td>\n",
       "      <td>-34.713</td>\n",
       "      <td>-17.648</td>\n",
       "      <td>1.748</td>\n",
       "      <td>-6.8</td>\n",
       "      <td>4.97</td>\n",
       "      <td>-0.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>25954 rows × 35 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 8
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-05-13T04:24:40.937521Z",
     "start_time": "2024-05-13T04:24:31.994661Z"
    }
   },
   "cell_type": "code",
   "source": [
    "del all_data[\"质押日期\"]\n",
    "all_data[\"质押状态\"]=all_data[\"质押状态\"].apply(lambda x:{\n",
    "    \"未达预警线\":0,\n",
    "    \"达到预警线\":1,\n",
    "    \"达到平仓线\":2\n",
    "}[x])\n",
    "\n",
    "all_data.to_excel(\"../../all_data.xlsx\", index=False)"
   ],
   "id": "56b14385a1177640",
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\huhu\\AppData\\Local\\Temp\\ipykernel_33984\\2770691255.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  all_data[\"质押状态\"]=all_data[\"质押状态\"].apply(lambda x:{\n"
     ]
    }
   ],
   "execution_count": 11
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
